﻿using HisPro.CommonUtils;
using HisPro.CommonUtils.Extention;
using HisPro.DataRepository;
using CNative.DbUtils;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CNative.EntityBuilder.DAL
{
    /// <summary>
    /// 数据库操作抽象帮助类
    /// </summary>
    public class DbHelper : IDbHelper
    {
        private string _dbName;
        /// <summary>
        /// 连接字符串
        /// </summary>
        private string _connectString;
        private DbProviderFactory _dbProviderFactory = null;
        /// <summary>
        /// 数据库类型
        /// </summary>
        protected DatabaseType _dbType = DatabaseType.SqlServer;
        /// <summary>
        /// 数据库类型
        /// </summary>
        public DatabaseType DBType { get { return _dbType; } }
        DbTransaction _transaction = null;

        public DbHelper(string dbName)
        {
            _dbName = dbName;
            _connectString = GetConnectString(dbName);
            _dbProviderFactory = GetDbProviderFactory(_dbType);
        }
        public DbHelper(DatabaseType dbType, string dbNameOrconStr)
        {
            _dbType = dbType;
            _connectString = GetConStr(dbNameOrconStr);
            _dbProviderFactory = GetDbProviderFactory(_dbType);
        }

        #region 开始数据库事务
        /// <summary>
        /// 开始数据库事务
        /// </summary>
        /// <param name="isol">指定连接的事务锁定行为</param>
        public DbTransaction BeginTransaction(System.Data.IsolationLevel isol = IsolationLevel.ReadCommitted)
        {
            _transaction = GetDbConnection().BeginTransaction(isol);
            return _transaction;
        }
        #endregion

        #region 提交数据库事务
        /// <summary>
        /// 提交数据库事务
        /// </summary>
        public void Commit()
        {
            if (_transaction != null)
            {
                _transaction.Commit();
                try
                {
                    if (_transaction.Connection != null)
                    {
                        _transaction.Connection.Close();
                        _transaction.Connection.Dispose();
                    }
                }
                catch { }
                _transaction.Dispose();
                _transaction = null;
            }
        }
        #endregion

        #region 回滚事务
        /// <summary>
        ///  从挂起状态回滚事务
        /// </summary>
        public void Rollback()
        {
            try
            {
                if (_transaction != null)
                {
                    _transaction.Rollback();

                    if (_transaction.Connection != null)
                    {
                        _transaction.Connection.Close();
                        _transaction.Connection.Dispose();
                    }
                    _transaction.Dispose();
                    _transaction = null;
                }
            }
            catch { }
        }
        #endregion

        #region 外部接口
        /// <summary>
        /// 获取提供工厂
        /// </summary>
        /// <param name="dbType">数据库类型</param>
        /// <returns></returns>
        public static DbProviderFactory GetDbProviderFactory(DatabaseType dbType)
        {
            DbProviderFactory factory = null;
            switch (dbType)
            {
                case DatabaseType.SqlServer: factory = SqlClientFactory.Instance; break;
                case DatabaseType.MySql: factory = MySqlClientFactory.Instance; break;
                //case DatabaseType.PostgreSql: factory = NpgsqlFactory.Instance; break;
                case DatabaseType.Oracle: factory = Devart.Data.Oracle.OracleProviderFactory.Instance; break;// OracleClientFactory.Instance; break;
                default: throw new Exception("请传入有效的数据库！");
            }

            return factory;
        }

        /// <summary>
        /// 获取一个连接
        /// </summary>
        /// <returns>返回一个合适的连接</returns>
        private DbConnection GetDbConnection()
        {
            DbConnection connection = _dbProviderFactory.CreateConnection();
            connection.ConnectionString = _connectString;
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
            return connection;
        }
        /// <summary>
        /// 获取DbConnection
        /// </summary>
        /// <param name="dbType">数据库类型</param>
        /// <returns></returns>
        public static DbConnection GetDbConnection(DatabaseType dbType)
        {
            return GetDbProviderFactory(dbType).CreateConnection();
        }

        /// <summary>
        /// 获取DbCommand
        /// </summary>
        /// <param name="dbType">数据库类型</param>
        /// <returns></returns>
        public static DbCommand GetDbCommand(DatabaseType dbType)
        {
            return GetDbProviderFactory(dbType).CreateCommand();
        }

        /// <summary>
        /// 获取DbParameter
        /// </summary>
        /// <param name="dbType">数据库类型</param>
        /// <returns></returns>
        public static System.Data.Common.DbParameter GetDbParameter(DatabaseType dbType)
        {
            return GetDbProviderFactory(dbType).CreateParameter();
        }

        /// <summary>
        /// 获取DataAdapter
        /// </summary>
        /// <param name="dbType">数据库类型</param>
        /// <returns></returns>
        public static DataAdapter GetDataAdapter(DatabaseType dbType)
        {
            return GetDbProviderFactory(dbType).CreateDataAdapter();
        }

        /// <summary>
        /// 将数据库类型字符串转换为对应的数据库类型
        /// </summary>
        /// <param name="dbTypeStr">数据库类型字符串</param>
        /// <returns></returns>
        public static DatabaseType DbTypeStrToDbType(string dbTypeStr)
        {
            if (dbTypeStr.IsNullOrEmpty())
                throw new Exception("请输入数据库类型字符串！");
            else
            {
                switch (dbTypeStr.ToLower())
                {
                    case "sqlserver": return DatabaseType.SqlServer;
                    case "mysql": return DatabaseType.MySql;
                    case "oracle": return DatabaseType.Oracle;
                    case "postgresql": return DatabaseType.PostgreSql;
                    default: throw new Exception("请输入合法的数据库类型字符串！");
                }
            }
        }

        /// <summary>
        /// 将数据库类型转换为对应的数据库类型字符串
        /// </summary>
        /// <param name="dbType">数据库类型</param>
        /// <returns></returns>
        public static string DbTypeToDbTypeStr(DatabaseType dbType)
        {
            if (dbType.IsNullOrEmpty())
                throw new Exception("请输入数据库类型！");
            else
            {
                switch (dbType)
                {
                    case DatabaseType.SqlServer: return "SqlServer";
                    case DatabaseType.MySql: return "MySql";
                    case DatabaseType.Oracle: return "Oracle";
                    case DatabaseType.PostgreSql: return "PostgreSql";
                    default: throw new Exception("请输入合法的数据库类型！");
                }
            }
        }

        /// <summary>
        /// 通过连接名或连接字符串获取连接字符串
        /// </summary>
        /// <param name="nameOrconStr">连接名或者连接字符串</param>
        /// <returns></returns>
        private string GetConStr(string nameOrconStr)
        {
            string conStr = string.Empty;
            string nameOfDbcon = string.Empty;
            //若为连接字符串
            if (nameOrconStr.Contains(";"))
                conStr = nameOrconStr;
            //若为"name=BaseDb"形式
            else if (nameOrconStr.Contains("name="))
            {
                var strArray = nameOrconStr.Split("=".ToArray());
                nameOfDbcon = strArray[1];
            }
            //为连接名
            else
            {
                nameOfDbcon = nameOrconStr;
            }

            if (!nameOfDbcon.IsNullOrEmpty())
            {
                _dbName = nameOfDbcon;
                var configSettings = ConfigurationManager.ConnectionStrings[nameOfDbcon];
                if (configSettings == null)
                    throw new Exception("未找到名称为[" + nameOfDbcon + "]的连接字符串");
                conStr = configSettings.ConnectionString;
            }
            return conStr;
        }
        /// <summary>
        /// 通过连接名或连接字符串获取连接字符串
        /// </summary>
        /// <param name="nameOrconStr">连接名或者连接字符串</param>
        /// <returns></returns>
        protected virtual string GetConnectString(string DBname)
        {
            string ConnStr = string.Empty;
            var c = System.Configuration.ConfigurationManager.ConnectionStrings[DBname];
            if (c != null)
            {
                switch (c.ProviderName)
                {
                    default:
                        _dbType = DatabaseType.SqlServer;
                        break;
                    case "System.Data.SqlClient":
                        _dbType = DatabaseType.SqlServer;
                        break;
                    case "System.Data.OracleClient":
                        _dbType = DatabaseType.Oracle;
                        break;
                    case "System.Data.MySql":
                        _dbType = DatabaseType.MySql;
                        break;
                }
                ConnStr = c.ConnectionString;
            }
            else
            {
                throw new Exception("未找到名称为" + DBname + "的数据库配置节点");
            }

            return ConnStr;
        }
        #endregion

        public virtual bool Execute(SqlEntity sql)
        {
            try
            {
                using (var cmd = _dbProviderFactory.CreateCommand())
                {
                    //如果transaction为空，则创建一个新的连接,否则就使用transaction的连接，以实现事务
                    if (_transaction == null)
                    {
                        cmd.Connection = GetDbConnection();
                    }
                    else
                    {
                        cmd.Transaction = _transaction;
                        cmd.Connection = _transaction.Connection;
                    }
                    cmd.CommandText = sql.Sql;
                    BindParameters(cmd, sql);
                    cmd.ExecuteNonQuery();

                    if (_transaction == null)
                    {
                        cmd.Connection.Close();
                        cmd.Connection.Dispose();
                    }
                    return true;
                }
            }
            catch (Exception e)
            {
                Rollback();
                throw e;
            }
        }
        public bool Execute(string sql, List<IDataParameter> parameters)
        {
            var sqlEntity = new SqlEntity();
            sqlEntity.Sql = sql;
            sqlEntity.Parameters = parameters;
            return Execute(sqlEntity);
        }

        public virtual bool Execute(List<SqlEntity> sqlList)
        {
            if (sqlList == null) return false;
            sqlList.ForEach(sql => Execute(sql));
            return true;
        }

        public virtual DataSet QueryDataSet(SqlEntity sql)
        {
            try
            {
                using (var cmd = _dbProviderFactory.CreateCommand())
                {
                    //如果transaction为空，则创建一个新的连接,否则就使用transaction的连接，以实现事务
                    if (_transaction == null)
                    {
                        cmd.Connection = GetDbConnection();
                    }
                    else
                    {
                        cmd.Transaction = _transaction;
                        cmd.Connection = _transaction.Connection;
                    }
                    // cmd.Connection = con;
                    cmd.CommandText = sql.Sql;
                    BindParameters(cmd, sql);
                    using (var adapt = _dbProviderFactory.CreateDataAdapter())
                    {
                        adapt.SelectCommand = cmd;
                        var ds = new DataSet();
                        adapt.Fill(ds);

                        if (_transaction == null)
                        {
                            cmd.Connection.Close();
                            cmd.Connection.Dispose();
                        }
                        return ds;
                    }
                }
            }
            catch (Exception err)
            {
                Rollback();
                throw err;
            }
        }

        public DataTable QueryDataTable(SqlEntity sql)
        {
            return QueryDataSet(sql).Tables[0];
        }

        public List<T> Query<T>(SqlEntity sql) where T : new()
        {
            var dt = QueryDataTable(sql);
            return FuncTable2Entity.DataTableToList<T>(dt);
        }

        public List<T> Query<T>(string sql, List<IDataParameter> parameters) where T : new()
        {
            var sqlEntity = new SqlEntity();
            sqlEntity.Sql = sql;
            sqlEntity.Parameters = parameters;
            var dt = QueryDataTable(sqlEntity);
            return FuncTable2Entity.DataTableToList<T>(dt);
        }

        public virtual T GetSingle<T>(SqlEntity sql) where T : new()
        {
            try
            {
                using (var cmd = _dbProviderFactory.CreateCommand())
                {//如果transaction为空，则创建一个新的连接,否则就使用transaction的连接，以实现事务
                    if (_transaction == null)
                    {
                        cmd.Connection = GetDbConnection();
                    }
                    else
                    {
                        cmd.Transaction = _transaction;
                        cmd.Connection = _transaction.Connection;
                    }
                    //cmd.Connection = con;
                    cmd.CommandText = sql.Sql;
                    BindParameters(cmd, sql);
                    var res = cmd.ExecuteScalar();

                    if (_transaction == null)
                    {
                        cmd.Connection.Close();
                        cmd.Connection.Dispose();
                    }
                    return (T)Convert.ChangeType(res, typeof(T));
                }
            }
            catch (Exception err)
            {
                Rollback();
                throw err;
            }
        }
        public T GetSingleRow<T>(SqlEntity sql) where T : new()
        {
            var dt = QueryDataTable(sql);
            return dt.DataTableToList<T>().FirstOrDefault();
        }
        protected virtual void BindParameters(DbCommand cmd, SqlEntity sql)
        {
            if (sql.Parameters == null || sql.Parameters.Count == 0) return;
            cmd.Parameters.AddRange(sql.Parameters.ToArray());
        }

    }
}
